﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace AIC_CD
{
    public partial class BaoCaoKetQuaSuDungVonVayCDQP_Update : MyFromBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            CheckPermission();
            if (!IsPostBack)
            {
                Config();
                BindDataToControls();
                BindDataToGrid();
            }
        }

        private void CheckPermission()
        {
            Dictionary<string, int> Role = MyCommon.CheckPermission("KetQuaSuDungVonVayCDQP");
            if (Role == null || Role["Xem"] == 0 || ((Role["Xem"] == 1 && Role["Sua"] == 0)))
            {
                Response.Redirect("Index.aspx?p=0");
            }
        }

        private void Config()
        {
            ContentPlaceHolder MainContent = this.Page.Master.FindControl("MainContent") as ContentPlaceHolder;
            for (int i = 3; i <= 15; i++)
            {
                string ControlName = "TextBox" + (i - 2);
                TextBox txtField = (TextBox)MainContent.FindControl(ControlName);
                txtField.Text = "";
            }
        }

        protected void btnLuu_Click(object sender, EventArgs e)
        {
            String sql = "SELECT * FROM dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS WHERE KyBaoCaoID_DM = @KyBaoCaoID_DM AND DonViID = @DonViID AND IsCommon = 1";
            List<SqlParameter> lstParameters = new List<SqlParameter>();
            lstParameters.Add(SqlHelper.CreateParameterObject("@KyBaoCaoID_DM", SqlDbType.Int, ParameterDirection.Input, cboKyBaoCao.SelectedValue));
            lstParameters.Add(SqlHelper.CreateParameterObject("@DonViID", SqlDbType.Int, ParameterDirection.Input, MyCommon.GetDonViID(Session["userid"].ToString())));

            DataTable dt = SqlHelper.ExecuteDataTable(CommandType.Text, sql, lstParameters);
            if (dt.Rows.Count == 0)
            {
                btnLuu.CommandName = MyConst.Action.Addnew;
            }
            else
            {
                btnLuu.CommandName = MyConst.Action.Update;
            }
            string fieldBuilder = "";
            string paramBuilder = "";
            string updateBuilder = "";
            for (int i = 1; i <= 13; i++)
            {
                fieldBuilder += "CM_" + i + ",";
                paramBuilder += "@CM_" + i + ",";
                updateBuilder += "CM_" + i + "=@CM_" + i + ",";
            }

            if (btnLuu.CommandName == MyConst.Action.Addnew)
            {
                string sSQL = "INSERT INTO dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS( KyBaoCaoID_DM ,DonViID, SoBaoCao ," + fieldBuilder +
                              "CM_ThoiGianBaoCao ,CM_TenBaoCao ,CM_ThuTruong, CreateDate ,UpdateDate ,UserID,IsCommon) " +
                              "VALUES (@KyBaoCaoID_DM,@DonViID,@SoBaoCao," + paramBuilder +
                              "GETDATE(),@CM_TenBaoCao,@CM_ThuTruong,GETDATE(),GETDATE(),@UserID,1)";

                List<SqlParameter> lstParameter = new List<SqlParameter>();
                lstParameter.Add(SqlHelper.CreateParameterObject("@KyBaoCaoID_DM", SqlDbType.Int, ParameterDirection.Input, cboKyBaoCao.SelectedValue));
                lstParameter.Add(SqlHelper.CreateParameterObject("@DonViID", SqlDbType.Int, ParameterDirection.Input, MyCommon.GetDonViID(Session["userid"].ToString())));
                lstParameter.Add(SqlHelper.CreateParameterObject("@SoBaoCao", SqlDbType.NVarChar, ParameterDirection.Input, txtSoBaoCao.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_1", SqlDbType.NVarChar, ParameterDirection.Input, TextBox1.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_2", SqlDbType.NVarChar, ParameterDirection.Input, TextBox2.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_3", SqlDbType.NVarChar, ParameterDirection.Input, TextBox3.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_4", SqlDbType.NVarChar, ParameterDirection.Input, TextBox4.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_5", SqlDbType.NVarChar, ParameterDirection.Input, TextBox5.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_6", SqlDbType.NVarChar, ParameterDirection.Input, TextBox6.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_7", SqlDbType.NVarChar, ParameterDirection.Input, TextBox7.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_8", SqlDbType.NVarChar, ParameterDirection.Input, TextBox8.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_9", SqlDbType.NVarChar, ParameterDirection.Input, TextBox9.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_10", SqlDbType.NVarChar, ParameterDirection.Input, TextBox10.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_11", SqlDbType.NVarChar, ParameterDirection.Input, TextBox11.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_12", SqlDbType.NVarChar, ParameterDirection.Input, TextBox12.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_13", SqlDbType.NVarChar, ParameterDirection.Input, TextBox13.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_TenBaoCao", SqlDbType.NVarChar, ParameterDirection.Input, txtTenBaoCao.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_ThuTruong", SqlDbType.NVarChar, ParameterDirection.Input, txtThuTruong.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@UserID", SqlDbType.Int, ParameterDirection.Input, Convert.ToInt32(Session["userid"])));

                SqlHelper.ExecuteNonQuery(CommandType.Text, sSQL, lstParameter);
                MyCommon.AddMessage(this.Page.Master, "Thêm mới báo cáo thành công", MyConst.MessageType.Success);
            }
            else
            {
                string sSQL = "Update tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS Set CM_TenBaoCao=@CM_TenBaoCao,SoBaoCao = @SoBaoCao, " + updateBuilder + "CM_ThuTruong= @CM_ThuTruong,UpdateDate = GETDATE(),UserID = @UserID " +
                                "Where DonViID=" + MyCommon.GetDonViID(Session["userid"].ToString()) + " And KyBaoCaoID_DM=" + cboKyBaoCao.SelectedValue + " AND IsCommon=1";

                List<SqlParameter> lstParameter = new List<SqlParameter>();
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_TenBaoCao", SqlDbType.NVarChar, ParameterDirection.Input, txtTenBaoCao.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@SoBaoCao", SqlDbType.NVarChar, ParameterDirection.Input, txtSoBaoCao.Text.Trim()));
                lstParameter.Add(SqlHelper.CreateParameterObject("@CM_ThuTruong", SqlDbType.NVarChar, ParameterDirection.Input, txtThuTruong.Text.Trim()));
                ContentPlaceHolder MainContent = this.Page.Master.FindControl("MainContent") as ContentPlaceHolder;
                for (int i = 1; i <= 13; i++)
                {
                    string ControlName = "TextBox" + i;
                    TextBox txtField = (TextBox)MainContent.FindControl(ControlName);
                    lstParameter.Add(SqlHelper.CreateParameterObject("@CM_" + i, SqlDbType.NVarChar, ParameterDirection.Input, txtField.Text.Trim()));
                }
                lstParameter.Add(SqlHelper.CreateParameterObject("@UserID", SqlDbType.Int, ParameterDirection.Input, Session["userid"]));


                SqlHelper.ExecuteNonQuery(CommandType.Text, sSQL, lstParameter);

                MyCommon.AddMessage(this.Page.Master, "Lưu thông tin báo cáo thành công", MyConst.MessageType.Success);
            }
        }

        protected void cboKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboKyBaoCao.SelectedValue != "0")
            {
                ResetField();
                btnLuu.Visible = true;
                BaoCaoDinhKem.Visible = true;
                BindDataToGrid();
            }
            else
            {
                btnLuu.Visible = false;
                BaoCaoDinhKem.Visible = false;
            }
        }

        private void ResetField()
        {
            txtSoBaoCao.Text = "";
            txtTenBaoCao.Text = "Báo cáo kết quả sử dụng vốn vay từ Quỹ trợ vốn Công đoàn Quốc Phòng";
            txtThuTruong.Text = "";
            ContentPlaceHolder MainContent = this.Page.Master.FindControl("MainContent") as ContentPlaceHolder;
            for (int i = 1; i <= 13; i++)
            {
                string ControlName = "TextBox" + i;
                TextBox txtField = (TextBox)MainContent.FindControl(ControlName);
                txtField.Text = "";
            }
        }

        private void BindDataToTextField(DataTable dt)
        {
            ContentPlaceHolder MainContent = this.Page.Master.FindControl("MainContent") as ContentPlaceHolder;
            for (int i = 3; i <= 15; i++)
            {
                string ControlName = "TextBox" + (i - 2);
                TextBox txtField = (TextBox)MainContent.FindControl(ControlName);
                txtField.Text = dt.Rows[0][i + 1].ToString();
            }
            txtSoBaoCao.Text = dt.Rows[0]["SoBaoCao"].ToString();
            txtThuTruong.Text = dt.Rows[0]["CM_ThuTruong"].ToString();
            txtTenBaoCao.Text = dt.Rows[0]["CM_TenBaoCao"].ToString();
        }

        /// <summary>
        /// Bind Data vào các contrls như Combobox, ListBox, textBox ...
        /// </summary>
        private void BindDataToControls()
        {
            //TODO
            if (Session["userid"] != null)
            {

                string sql = "Select DV.* " +
                             "From tblDonVi DV " +
                             "Inner Join tblUser U On U.DonViID=DV.ID " +
                             "Where U.ID=" + Session["userid"];
                DataTable dtDV = SqlHelper.ExecuteDataTable(CommandType.Text, sql);
                lblDonVi.Text = "(" + dtDV.Rows[0]["MaDV"] + ") " + dtDV.Rows[0]["TenDV"];

                sql = "Select ID,TenDM From tblDanhMuc Where MaDM='" + MyConst.DanhMuc.KyBaoCao.Ma + "' ";
                SqlDataReader reKyBaoCao = SqlHelper.ExecuteReader(CommandType.Text, sql);
                clsDataBind.CreateCombo("Tất cả", reKyBaoCao, cboKyBaoCao, 1, 0);
            }
        }

        protected void grvData_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            RowDataBound(sender, e);
        }

        protected void grvData_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            grvData.EditIndex = -1;
            BindDataToGrid();
        }

        private void BindDataToGrid()
        {
            bool IsEmpty = false;
            List<SqlParameter> arrParams = new List<SqlParameter>();
            arrParams.Add(SqlHelper.CreateParameterObject("@KyBaoCaoID_DM", SqlDbType.Int, ParameterDirection.Input, cboKyBaoCao.SelectedValue));
            arrParams.Add(SqlHelper.CreateParameterObject("@DonViID", SqlDbType.Int, ParameterDirection.Input, MyCommon.GetDonViID(Session["userid"].ToString())));

            string sql = "SELECT * FROM dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS " +
                         "WHERE DonViID = @DonViID AND KyBaoCaoID_DM = @KyBaoCaoID_DM AND IsCommon <>1";
            DataTable dt = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParams);
            if (dt.Rows.Count == 0)
            {
                IsEmpty = true;
                btnLuu.CommandName = MyConst.Action.Addnew;
                DataRow dr = dt.NewRow();
                dr["F1"] = "";
                dr["F2"] = 0;
                dr["F3"] = "";
                dr["F4"] = "";
                dr["F5"] = 0;
                dr["F6"] = 0;
                dr["F7"] = 0;
                dt.Rows.InsertAt(dr, 0);
                ResetField();
            }
            else
            {
                BindDataToTextField();
                btnLuu.CommandName = MyConst.Action.Update;
            }
            grvData.DataSource = dt;
            grvData.DataBind();
            if (IsEmpty)
            {
                grvData.Rows[0].Visible = false;
            }
        }

        private void BindDataToTextField()
        {
            string sql = "SELECT * FROM dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS " +
                         "WHERE KyBaoCaoID_DM = @KyBaoCaoID_DM AND DonViID = @DonViID AND IsCommon = 1";
            List<SqlParameter> lstParameters = new List<SqlParameter>();
            lstParameters.Add(SqlHelper.CreateParameterObject("@KyBaoCaoID_DM", SqlDbType.Int, ParameterDirection.Input, cboKyBaoCao.SelectedValue));
            lstParameters.Add(SqlHelper.CreateParameterObject("@DonViID", SqlDbType.Int, ParameterDirection.Input, MyCommon.GetDonViID(Session["userid"].ToString())));
            DataTable dt = SqlHelper.ExecuteDataTable(CommandType.Text, sql, lstParameters);
            if (dt.Rows.Count != 0)
            {
                txtTenBaoCao.Text = dt.Rows[0]["CM_TenBaoCao"].ToString();
                txtThuTruong.Text = dt.Rows[0]["CM_ThuTruong"].ToString();
                txtSoBaoCao.Text = dt.Rows[0]["SoBaoCao"].ToString();
                ContentPlaceHolder MainContent = this.Page.Master.FindControl("MainContent") as ContentPlaceHolder;
                for (int i = 1; i <= 13; i++)
                {
                    string ControlName = "TextBox" + i;
                    TextBox txtField = (TextBox)MainContent.FindControl(ControlName);
                    txtField.Text = dt.Rows[0]["CM_" + i].ToString();
                }
            }
        }

        protected void grvData_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            string sID = e.CommandArgument.ToString().Trim();
            string sCommandName = e.CommandName;


            if ((sCommandName == AIC_CD.MyConst.Action.Approve))
            {
                SqlTransaction MyTrans = SqlHelper.BeginTransaction();
                try
                {

                    // cap nhat vao database
                    TextBox txtF1Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF1Edit");
                    TextBox txtF2Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF2Edit");
                    TextBox txtF3Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF3Edit");
                    TextBox txtF4Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF4Edit");
                    TextBox txtF5Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF5Edit");
                    TextBox txtF6Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF6Edit");
                    TextBox txtF7Edit = (TextBox)grvData.Rows[grvData.EditIndex].FindControl("txtF7Edit");

                    string sSQL = "UPDATE dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS SET " +
                                  "F1 = @F1,F2 = @F2,F3 = @F3,F4 = @F4,F5 = @F5,F6 = @F6, " +
                                  "F7 = @F7,UpdateDate = GETDATE(),UserID = @UserID WHERE ID = @ID";

                    List<SqlParameter> lstParameter = new List<SqlParameter>();
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F1", SqlDbType.NVarChar, ParameterDirection.Input, txtF1Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F2", SqlDbType.Float, ParameterDirection.Input, txtF2Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F3", SqlDbType.NVarChar, ParameterDirection.Input, txtF3Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F4", SqlDbType.NVarChar, ParameterDirection.Input, txtF4Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F5", SqlDbType.Float, ParameterDirection.Input, txtF5Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F6", SqlDbType.Float, ParameterDirection.Input, txtF6Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F7", SqlDbType.Float, ParameterDirection.Input, txtF7Edit.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@UserID", SqlDbType.Int, ParameterDirection.Input, Session["userid"]));

                    lstParameter.Add(SqlHelper.CreateParameterObject("@ID", SqlDbType.Int, ParameterDirection.Input, sID));

                    SqlHelper.ExecuteNonQuery(CommandType.Text, sSQL, lstParameter);
                    MyTrans.Commit();
                    grvData.EditIndex = -1;
                    BindDataToGrid();
                    MyCommon.AddMessage(this.Master, "Cập nhật thành công !", MyConst.MessageType.Success);
                }
                catch (Exception)
                {
                    MyCommon.AddMessage(this.Page.Master, "Dữ liệu nhập vào chưa đúng, lưu ý một số thông tin bắt buộc phải là số, không được nhập chữ", MyConst.MessageType.Warning);
                    MyTrans.Rollback();
                }

            }
            else if (e.CommandName == AIC_CD.MyConst.Action.Addnew)
            {
                SqlTransaction MyTrans = SqlHelper.BeginTransaction();
                try
                {
                    TextBox txF1New = (TextBox)grvData.FooterRow.FindControl("txtF1New");
                    TextBox txF2New = (TextBox)grvData.FooterRow.FindControl("txtF2New");
                    TextBox txF3New = (TextBox)grvData.FooterRow.FindControl("txtF3New");
                    TextBox txF4New = (TextBox)grvData.FooterRow.FindControl("txtF4New");
                    TextBox txF5New = (TextBox)grvData.FooterRow.FindControl("txtF5New");
                    TextBox txF6New = (TextBox)grvData.FooterRow.FindControl("txtF6New");
                    TextBox txF7New = (TextBox)grvData.FooterRow.FindControl("txtF7New");

                    string sql = "INSERT INTO dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS" +
                                 "(KyBaoCaoID_DM,DonViID,F1,F2,F3,F4,F5,F6,F7,CreateDate,UpdateDate,UserID,IsCommon)" +
                                 "VALUES(@KyBaoCaoID_DM,@DonViID,@F1,@F2,@F3,@F4,@F5,@F6,@F7,GETDATE(),GETDATE(),@UserID,0)";
                    List<SqlParameter> lstParameter = new List<SqlParameter>();
                    lstParameter.Add(SqlHelper.CreateParameterObject("@KyBaoCaoID_DM", SqlDbType.Int, ParameterDirection.Input, Convert.ToInt32(cboKyBaoCao.SelectedValue)));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@DonViID", SqlDbType.Int, ParameterDirection.Input, MyCommon.GetDonViID(Session["userid"].ToString())));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F1", SqlDbType.NVarChar, ParameterDirection.Input, txF1New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F2", SqlDbType.Float, ParameterDirection.Input, txF2New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F3", SqlDbType.NVarChar, ParameterDirection.Input, txF3New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F4", SqlDbType.NVarChar, ParameterDirection.Input, txF4New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F5", SqlDbType.Float, ParameterDirection.Input, txF5New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F6", SqlDbType.Float, ParameterDirection.Input, txF6New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@F7", SqlDbType.Float, ParameterDirection.Input, txF7New.Text.Trim()));
                    lstParameter.Add(SqlHelper.CreateParameterObject("@UserID", SqlDbType.Int, ParameterDirection.Input, Convert.ToInt32(Session["userid"].ToString())));

                    int Result = SqlHelper.ExecuteNonQuery(CommandType.Text, sql, lstParameter);
                    if (Result == 0)
                    {
                        MyCommon.AddMessage(this.Master, "Có lỗi xảy ra trong quá trình thêm mới", MyConst.MessageType.Error);
                    }
                    else
                    {
                        MyCommon.AddMessage(this.Master, "Thêm mới thành công !", MyConst.MessageType.Success);
                    }

                    MyTrans.Commit();
                    BindDataToGrid();
                }
                catch (Exception)
                {
                    MyCommon.AddMessage(this.Page.Master, "Dữ liệu nhập vào chưa đúng, lưu ý một số thông tin bắt buộc phải là số, không được nhập chữ", MyConst.MessageType.Warning);
                    MyTrans.Rollback();
                }
            }
            else if ((sCommandName == AIC_CD.MyConst.Action.Delete))
            {
                string sql = "DELETE FROM dbo.tblBaoCaoKetQuaSuDungVonVayTuQuyCDCS WHERE ID = @ID";
                List<SqlParameter> lstParameter = new List<SqlParameter>();

                lstParameter.Add(SqlHelper.CreateParameterObject("@ID", SqlDbType.Int, ParameterDirection.Input, sID));
                SqlHelper.ExecuteNonQuery(CommandType.Text, sql, lstParameter);

                grvData.EditIndex = -1;
                BindDataToGrid();
                MyCommon.AddMessage(this.Master, "Xóa thành công !", MyConst.MessageType.Success);
            }
        }

        protected void grvData_RowEditing(object sender, GridViewEditEventArgs e)
        {
            grvData.EditIndex = e.NewEditIndex;
            BindDataToGrid();
        }

        protected void grvData_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            grvData.DeleteRow(e.RowIndex);
            grvData.DataBind();
        }
    }
}
